"""
Optimized Telemetry API Routes
Uses new consolidated database architecture for better performance
"""

from typing import List, Optional
from datetime import datetime
from fastapi import APIRouter, Depends, HTTPException, status, Query
from pydantic import BaseModel, Field
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, and_, or_, text, case
import fnmatch
import json
import uuid

from app.core.database import get_business_db, get_extractor_db
from app.core.security import get_current_user
from app.models.business import Team, TeamFileAssignment, MetricTeamAssignment
from app.models.extractor import Metric, MetricOwner

router = APIRouter()


# Optimized Pydantic models matching new database structure
class OptimizedTelemetryPointResponse(BaseModel):
    """Optimized telemetry point response model"""
    id: int
    name: str
    type: str
    description: Optional[str] = None
    units: Optional[str] = None
    component: Optional[str] = None
    file_path: str
    line_number: Optional[int] = None
    owner: Optional[str] = None
    assigned_team_id: Optional[str] = None
    assigned_team_name: Optional[str] = None
    analysis_status: str = "NOT_ANALYZED"
    analysis_notes: Optional[str] = None
    analyzed_by: Optional[str] = None
    analyzed_at: Optional[str] = None
    metadata: Optional[dict] = Field(default_factory=dict)
    assignment_reason: Optional[str] = None

    class Config:
        from_attributes = True


class OptimizedTelemetryListResponse(BaseModel):
    """Optimized telemetry list response model"""
    success: bool
    data: List[OptimizedTelemetryPointResponse]
    total: int
    page: int
    page_size: int
    message: Optional[str] = None


class OptimizedTelemetryStatsResponse(BaseModel):
    """Optimized telemetry statistics response"""
    success: bool
    data: dict
    message: Optional[str] = None


async def get_team_info_for_metric(
    metric_id: int,
    business_db: AsyncSession
) -> Optional[dict]:
    """Get team assignment info using optimized query"""
    # Use the new consolidated ownership approach
    query = text("""
        SELECT DISTINCT
            mta.team_name,
            mta.assignment_reason,
            mta.assigned_at
        FROM metric_team_assignments mta
        WHERE mta.metric_id = :metric_id
        LIMIT 1
    """)

    result = await business_db.execute(query, {"metric_id": metric_id})
    row = result.first()

    if row:
        return {
            "team_name": row.team_name,
            "assignment_reason": row.assignment_reason,
            "assigned_at": row.assigned_at.isoformat() if row.assigned_at else None
        }

    return None


async def get_owner_info_for_metric(
    metric_id: int,
    extractor_db: AsyncSession
) -> Optional[str]:
    """Get owner info using optimized query"""
    query = select(MetricOwner.owner_email).where(
        MetricOwner.metric_id == metric_id
    ).limit(1)

    result = await extractor_db.execute(query)
    row = result.first()

    return row.owner_email if row else None


@router.get("/", response_model=OptimizedTelemetryListResponse)
async def get_telemetry_points_optimized(
    page: int = Query(1, ge=1, description="Page number"),
    page_size: int = Query(50, ge=1, le=1000, description="Items per page"),
    type_filter: Optional[str] = Query(None, description="Filter by metric type"),
    component_filter: Optional[str] = Query(None, description="Filter by component"),
    team_filter: Optional[str] = Query(None, description="Filter by team name or ID"),
    analysis_status_filter: Optional[str] = Query(None, description="Filter by analysis status"),
    search: Optional[str] = Query(None, description="Search in name, description, component"),
    sort_by: Optional[str] = Query("name", description="Sort field"),
    sort_order: Optional[str] = Query("asc", description="Sort order: asc or desc"),
    current_user: dict = Depends(get_current_user),
    business_db: AsyncSession = Depends(get_business_db),
    extractor_db: AsyncSession = Depends(get_extractor_db)
):
    """Get telemetry points with optimized performance using new database structure"""
    try:
        # Get team name if team_filter is a UUID
        team_name_filter = None
        if team_filter:
            if len(team_filter) > 20 and '-' in team_filter:  # Likely a UUID
                try:
                    from uuid import UUID
                    team_query = select(Team.name).where(Team.id == team_filter)
                    team_result = await business_db.execute(team_query)
                    team_row = team_result.first()
                    if team_row:
                        team_name_filter = team_row.name
                except Exception:
                    team_name_filter = None
            else:
                team_name_filter = team_filter

        # Build optimized base query using new metrics table structure
        # The analysis_status is now directly in the metrics table - no JOIN needed!
        base_query = select(Metric).outerjoin(
            MetricOwner, Metric.id == MetricOwner.metric_id
        )

        # Build filters efficiently
        filters = []

        if type_filter:
            filters.append(Metric.type == type_filter)

        if component_filter:
            filters.append(Metric.component == component_filter)

        if analysis_status_filter:
            # Direct filter on metrics table - much faster!
            filters.append(Metric.analysis_status == analysis_status_filter)

        if search:
            search_pattern = f"%{search}%"
            filters.append(
                or_(
                    Metric.name.ilike(search_pattern),
                    Metric.summary.ilike(search_pattern),
                    Metric.component.ilike(search_pattern),
                    Metric.file_path.ilike(search_pattern)
                )
            )

        # Apply team filter using subquery for efficiency
        if team_name_filter:
            team_subquery = select(MetricTeamAssignment.metric_id).where(
                MetricTeamAssignment.team_name == team_name_filter
            )
            filters.append(Metric.id.in_(team_subquery))

        # Apply all filters
        if filters:
            base_query = base_query.where(and_(*filters))

        # Count total records efficiently
        count_query = select(func.count(func.distinct(Metric.id))).select_from(Metric)

        # Apply same filters to count query
        if filters:
            if team_name_filter:
                # Handle team filter separately for count
                other_filters = [f for f in filters if not hasattr(f, 'right') or not callable(getattr(f.right, 'in_', None))]
                if other_filters:
                    count_query = count_query.where(and_(*other_filters))
                # Apply team filter separately
                team_subquery = select(MetricTeamAssignment.metric_id).where(
                    MetricTeamAssignment.team_name == team_name_filter
                )
                count_query = count_query.where(Metric.id.in_(team_subquery))
            else:
                count_query = count_query.where(and_(*filters))

        # Execute count query
        total_result = await extractor_db.execute(count_query)
        total = total_result.scalar() or 0

        # Apply sorting efficiently
        if sort_by == "name":
            order_col = Metric.name
        elif sort_by == "type":
            order_col = Metric.type
        elif sort_by == "component":
            order_col = Metric.component
        elif sort_by == "analysis_status":
            order_col = Metric.analysis_status
        else:
            order_col = Metric.name

        if sort_order.lower() == "desc":
            base_query = base_query.order_by(order_col.desc())
        else:
            base_query = base_query.order_by(order_col.asc())

        # Apply pagination
        offset = (page - 1) * page_size
        query_with_pagination = base_query.offset(offset).limit(page_size)

        # Execute main query
        result = await extractor_db.execute(query_with_pagination)
        metrics = result.unique().scalars().all()

        # Batch fetch team assignments to reduce database calls
        metric_ids = [m.id for m in metrics]

        # Get team assignments in batch
        team_assignments = {}
        if metric_ids:
            team_query = text("""
                SELECT
                    metric_id,
                    team_name,
                    assignment_reason
                FROM metric_team_assignments
                WHERE metric_id IN :metric_ids
            """).bindparams(metric_ids=tuple(metric_ids))

            team_result = await business_db.execute(team_query)
            for row in team_result:
                team_assignments[row.metric_id] = {
                    "team_name": row.team_name,
                    "assignment_reason": row.assignment_reason
                }

        # Get owners in batch
        owners = {}
        if metric_ids:
            owner_query = select(MetricOwner.metric_id, MetricOwner.owner_email).where(
                MetricOwner.metric_id.in_(metric_ids)
            )
            owner_result = await extractor_db.execute(owner_query)
            for row in owner_result:
                if row.metric_id not in owners:
                    owners[row.metric_id] = []
                owners[row.metric_id].append(row.owner_email)

        # Build response data efficiently
        telemetry_points = []
        for metric in metrics:
            team_info = team_assignments.get(metric.id)
            owner_list = owners.get(metric.id, [])

            telemetry_points.append(OptimizedTelemetryPointResponse(
                id=metric.id,
                name=metric.name,
                type=metric.type,
                description=metric.summary,
                units=metric.units,
                component=metric.component,
                file_path=metric.file_path,
                line_number=metric.line_number,
                owner=", ".join(owner_list) if owner_list else None,
                assigned_team_name=team_info["team_name"] if team_info else None,
                assignment_reason=team_info["assignment_reason"] if team_info else None,
                analysis_status=metric.analysis_status or "NOT_ANALYZED",
                analysis_notes=metric.analysis_notes,
                analyzed_by=metric.analyzed_by,
                analyzed_at=metric.analyzed_at.isoformat() if metric.analyzed_at else None,
                metadata={"created_at": metric.created_at.isoformat() if metric.created_at else None}
            ))

        return OptimizedTelemetryListResponse(
            success=True,
            data=telemetry_points,
            total=total,
            page=page,
            page_size=page_size
        )

    except Exception as e:
        print(f"❌ [OptimizedTelemetryAPI] Error: {e}")
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Internal server error: {str(e)}"
        )


@router.get("/stats/overview", response_model=OptimizedTelemetryStatsResponse)
async def get_telemetry_stats_optimized(
    current_user: dict = Depends(get_current_user),
    business_db: AsyncSession = Depends(get_business_db),
    extractor_db: AsyncSession = Depends(get_extractor_db)
):
    """Get optimized telemetry statistics using new database structure"""
    try:
        # Get all stats in a single query using the new metrics table structure
        stats_query = text("""
            SELECT
                COUNT(*) as total_metrics,
                COUNT(CASE WHEN type = 'histogram' THEN 1 END) as histogram_count,
                COUNT(CASE WHEN type = 'enum' THEN 1 END) as enum_count,
                COUNT(CASE WHEN type = 'ukm_event' THEN 1 END) as ukm_event_count,
                COUNT(CASE WHEN type = 'ukm_metric' THEN 1 END) as ukm_metric_count,
                COUNT(CASE WHEN analysis_status = 'ANALYZED' THEN 1 END) as analyzed_count,
                COUNT(CASE WHEN analysis_status = 'NOT_ANALYZED' THEN 1 END) as not_analyzed_count,
                COUNT(CASE WHEN analysis_status = 'NOT_NEEDED' THEN 1 END) as not_needed_count,
                COUNT(CASE WHEN analysis_status = 'NEEDS_COLLECTION' THEN 1 END) as needs_collection_count,
                COUNT(DISTINCT component) as unique_components,
                COUNT(CASE WHEN assigned.team_name IS NOT NULL THEN 1 END) as assigned_count
            FROM metrics m
            LEFT JOIN metric_team_assignments assigned ON m.id = assigned.metric_id
        """)

        result = await extractor_db.execute(stats_query)
        stats = result.first()

        # Get unique teams count
        teams_query = text("SELECT COUNT(DISTINCT team_name) FROM metric_team_assignments")
        teams_result = await business_db.execute(teams_query)
        unique_teams = teams_result.scalar() or 0

        # Get unassigned count
        unassigned = stats.total_metrics - stats.assigned_count

        # Build comprehensive stats object
        stats_data = {
            "total_metrics": stats.total_metrics,
            "by_type": {
                "histogram": stats.histogram_count,
                "enum": stats.enum_count,
                "ukm_event": stats.ukm_event_count,
                "ukm_metric": stats.ukm_metric_count
            },
            "top_components": {},  # Can be added later if needed
            "by_analysis_status": {
                "analyzed": stats.analyzed_count,
                "not_analyzed": stats.not_analyzed_count,
                "needs_collection": stats.needs_collection_count,
                "not_needed": stats.not_needed_count
            },
            "unassigned": unassigned,
            "unique_teams": unique_teams,
            "unique_components": stats.unique_components
        }

        return OptimizedTelemetryStatsResponse(
            success=True,
            data=stats_data,
            message="Stats retrieved successfully"
        )

    except Exception as e:
        print(f"❌ [OptimizedTelemetryAPI] Stats Error: {e}")
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Failed to retrieve statistics: {str(e)}"
        )


@router.get("/{telemetry_id}", response_model=OptimizedTelemetryPointResponse)
async def get_telemetry_point_optimized(
    telemetry_id: int,
    current_user: dict = Depends(get_current_user),
    business_db: AsyncSession = Depends(get_business_db),
    extractor_db: AsyncSession = Depends(get_extractor_db)
):
    """Get single telemetry point with optimized performance"""
    try:
        # Get metric with optimized query
        query = select(Metric).where(Metric.id == telemetry_id)
        result = await extractor_db.execute(query)
        metric = result.first()

        if not metric:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Telemetry point not found"
            )

        # Get owner info efficiently
        owner_query = select(MetricOwner.owner_email).where(
            MetricOwner.metric_id == telemetry_id
        )
        owner_result = await extractor_db.execute(owner_query)
        owners = [row.owner_email for row in owner_result]

        # Get team assignment efficiently
        team_query = text("""
            SELECT team_name, assignment_reason, assigned_at
            FROM metric_team_assignments
            WHERE metric_id = :metric_id
            LIMIT 1
        """)
        team_result = await business_db.execute(team_query, {"metric_id": telemetry_id})
        team_row = team_result.first()

        return OptimizedTelemetryPointResponse(
            id=metric.id,
            name=metric.name,
            type=metric.type,
            description=metric.summary,
            units=metric.units,
            component=metric.component,
            file_path=metric.file_path,
            line_number=metric.line_number,
            owner=", ".join(owners) if owners else None,
            assigned_team_name=team_row.team_name if team_row else None,
            assignment_reason=team_row.assignment_reason if team_row else None,
            analysis_status=metric.analysis_status or "NOT_ANALYZED",
            analysis_notes=metric.analysis_notes,
            analyzed_by=metric.analyzed_by,
            analyzed_at=metric.analyzed_at.isoformat() if metric.analyzed_at else None,
            metadata={
                "created_at": metric.created_at.isoformat() if metric.created_at else None,
                "updated_at": metric.updated_at.isoformat() if metric.updated_at else None
            }
        )

    except HTTPException:
        raise
    except Exception as e:
        print(f"❌ [OptimizedTelemetryAPI] Get point error: {e}")
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Failed to retrieve telemetry point: {str(e)}"
        )


# Performance monitoring endpoint
@router.get("/performance/health")
async def performance_health_check(
    current_user: dict = Depends(get_current_user),
    extractor_db: AsyncSession = Depends(get_extractor_db)
):
    """Health check endpoint for monitoring API performance"""
    try:
        # Simple query to test database performance
        start_time = datetime.now()

        query = select(func.count()).select_from(Metric)
        result = await extractor_db.execute(query)
        count = result.scalar()

        end_time = datetime.now()
        response_time_ms = (end_time - start_time).total_seconds() * 1000

        return {
            "status": "healthy",
            "response_time_ms": response_time_ms,
            "total_metrics": count,
            "timestamp": end_time.isoformat()
        }

    except Exception as e:
        return {
            "status": "unhealthy",
            "error": str(e),
            "timestamp": datetime.now().isoformat()
        }